<?php

declare(strict_types=1);

namespace app\common\tool\excel;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class Export
{
    /**
     * 数据导入
     * @param $url
     * @param $reader_name
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function excelImport($url, $reader_name)
    {
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($reader_name);
        $reader->setReadDataOnly(TRUE); //只读取数据，不读取格式
        $spreadsheet = $reader->load($url); //载入excel表格

        $sheet = $spreadsheet->getActiveSheet();
        $highestRow = $sheet->getHighestRow(); // 总行数
        $lines = $highestRow - 1;
        if ($lines <= 0) {
            return ['code' => -1, 'msg' => '导入数据为空！'];
        }
        $sheet = $spreadsheet->getActiveSheet();
        $res = array();
        // 获取行数据
        foreach ($sheet->getRowIterator(2) as $row) {  // 从第二行开始
            $tmp = array();
            // 循环列
            foreach ($row->getCellIterator() as $cell) {
                $tmp[] = $cell->getFormattedValue(); //获取单元格的值
            }
            if (!empty($tmp)) {
                $res[$row->getRowIndex()] = $tmp;
            }
        }
        return ['code' => 1, 'data' => $res, 'msg' => '导入成功'];
    }

    /**
     * 导出数据
     */
    public static function export($datatitle,$datalist, $filePath)
    {
        /**
         * 边框样式
         */
        $borders = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
                'inside' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ]
            ],
        ];
        // 创建新的Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 获取活动的工作表
        $sheet = $spreadsheet->getActiveSheet();
        // 自定义表头
        $r = range('A', 'C');
        $az = range('A', 'Z');
        foreach ($r as $k => $v) {
            foreach ($az as $vv) {
                $list[] = $v . $vv;
            }
        }
        $cellName = array_merge($az, $list);
        // 设置表头
        foreach ($cellName as $key => $header) {
            $sheet->getRowDimension(1)->setRowHeight(30); //序号高度
            $sheet->getColumnDimension($header)->setWidth(25);
        }
        /**设置表头*/
        foreach ($datatitle as $key => $header) {
            $sheet->setCellValue($cellName[$key] . '1', $header);
            $sheet->getStyle($cellName[$key] . '1')->applyFromArray( $borders, );
        }
        /**数据写入 */
        $column = 2;
        foreach ($datalist as $key => $rows) { // 行写入
            $sheet->getRowDimension($column)->setRowHeight(30); //序号高度
            foreach ($rows as $keyName => $value) { // 列写入
                $sheet->setCellValue($cellName[($keyName)] . $column, $value);
                $sheet->getStyle($cellName[($keyName)] . $column)->applyFromArray($borders)->getAlignment()->setWrapText(true);
            }
            $column++;
        }
        // 创建Xlsx文件写入器
        $writer = new Xlsx($spreadsheet);

        if (file_exists($filePath)) {
            unlink('./' . $filePath);
        }
        $writer->save($filePath);
        return '/' .$filePath;
    }
}
